SQL SERVER – Index Levels, Page Count, Record Count and DMV – sys.dm_db_index_physical_stats

In the recent Query Tuning project, one of the developers who were helping me out in the project asked me if there is any way that he could know how many pages are used by any Index,  and if there is any way I could demonstrate the different levels of B-Tree.

The following is the diagram on Clustered Index that I have quickly drawn using MS Word for the said developer.

SQL SERVER - Index Levels, Page Count, Record Count and DMV - sys.dm_db_index_physical_stats indextree
Clustered Index B-Tree

Let us quickly see the diagram of B-Tree and how the levels are set up. The leaf level is always considered as Level 0. There can be many levels of the intermediate nodes. In the example above, I have listed only one intermediate node for demonstration purposes.

We can use Dynamic Management Views to figure out how many different levels are there for any Index, as well as how many rows are stored at each level and the number of pages used for all Index.

Let us run the following commands and generate a table with data. We will create a Clustered Index so we can have B-Tree structure.

USE tempdb
GO
-- Create Table FragTable
CREATE TABLE FragTable (ID CHAR(800),
FirstName CHAR(2000),
LastName CHAR(3000),
City CHAR(2253))
GO
-- Create Clustered Index
CREATE CLUSTERED INDEX [IX_FragTable_ID] ON FragTable
(
[ID] ASC
) ON [PRIMARY] GO
-- Insert one Million Records
INSERT INTO FragTable (ID,FirstName,LastName,City)
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM
sys.all_objects a
CROSS JOIN sys.all_objects b
GO
-- Check the percentages
SELECT avg_page_space_used_in_percent
,avg_fragmentation_in_percent
,index_level
,record_count
,page_count
,fragment_count
,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('TempDb'),OBJECT_ID('FragTable'),NULL,NULL,'DETAILED')
GO
-- Clean up
DROP TABLE FragTable
GO

Now let us check the result-set of the table.

SQL SERVER - Index Levels, Page Count, Record Count and DMV - sys.dm_db_index_physical_stats indextree1

From the resultset above, we can see that there are multiple levels of the Index. In our example, we have 4 levels of Index, and each level has different numbers of  pages and rows. In one of the future articles I will post, we will analyze the result in a deeper sense.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL DMV, SQL Index, SQL Scripts
Previous Post
SQL SERVER – View XML Query Plans in SSMS as Graphical Execution Plan
Next Post
SQL SERVER – PowerShell Version Info

Related Posts

8 Comments. Leave new

  • Interesting.

    Reply
  • Do you know how to delete pages in sql related to a dnn site? Where do i look to in sql to find these pages? I have about 150 pages i need to delete and manually is very time consuming. I honestly can’t believe dnn does not have the feature of deleting multiple pages at the same time. I do not have a backup to restore to. If you know how, please PM my email. Thank you very much for your help and all your helpful posts.

    Reply
  • Hello Pinal,

    I’m a little dazzled.
    In theory, can i have multiples pages in a leaf level?

    best regads,

    Valmir

    Reply
  • How page_count effects the performance?

    Reply
  • i want to make mlm database.
    that table is this.
    create table mlm(
    MemberID int,
    RootID int ,
    Position varchar(2),
    Name varchar(10),
    )
    insert into mlm values(1,0,’Left’,’A’)
    insert into mlm values(2,1,’Centre’,’B’)
    insert into mlm values(3,1,’Right’,’C’)
    insert into mlm values(4,1,’Left’,’D)
    insert into mlm values(5,2,’Centre’,’E’)
    insert into mlm values(6,2,’Right’,’F’)
    /*i wan to count total left id of 1 Member ID that is 4 */
    2 is 1,
    .
    but how to count in sql server this system

    Reply
  • hi sir
    i want to manage the level of user for MLM
    plz give me solution of how to manage level

    ex-
    Memberid SponsorId
    100 NULL
    101 100
    102 100
    103 100
    104 101
    105 101
    106 101
    107 102
    108 102
    109 102
    110 103
    111 103
    112 103
    113 104


    from 104 to 113 has 0 level and 101,102 and 103 has been completed his 1st level and 100 has been completed his 2 level

    how to i manage the level of user dynamically after user registration

    plz help me sir

    Reply
    • database design —–

      Memberid ———-SponsorId

      100 —————– NULL
      101 —————– 100
      102 —————– 100
      103 —————– 100
      104 —————– 101
      105 —————– 101
      106 —————– 101
      107 —————– 102
      108 —————– 102
      109 —————– 102
      110 —————– 103
      111 —————– 103
      112 —————– 103
      113 —————– 104

      Reply

Leave a Reply